# coding=utf-8

import web

from web.utils import storage
from db import connection

db = connection.default

def delete_afflatus_by_id(id):
    """
    删除给定的灵感信息
    """
    return db.delete('afflatus'
            , where = 'id = $id'
            , vars = locals())

def add_afflatus_by_form(form):
    """
    通过表单添加一条灵感信息
    """
    return db.insert('afflatus'
            , proj_id = int(form.proj_id.value)
            , title = form.title.value
            , short_text = form.short_text.value
            , media_id = form.media_id.value
            , date_created = web.SQLLiteral('NOW()'))

def update_afflatus_by_form(id, form):
    """
    通过表单更新一条灵感信息
    """
    return db.update('afflatus'
            , proj_id = int(form.proj_id.value)
            , title = form.title.value
            , short_text = form.short_text.value
            , media_id = form.media_id.value
            , where = 'id = $id'
            , vars = locals())

def get_afflatus_by_id(id):
    """
    获取给定的灵感信息
    """
    sets = db.select('afflatus'
            , where = 'id = $id'
            , vars = locals())
    if sets:
        return sets[0]
    return None

def delete_afflatus(id):
    """
    删除给定的灵感信息
    """
    return db.delete('afflatus'
            , where = 'id = $id'
            , vars = locals())

def get_all_afflatus(offset, length):
    """
    分页获取灵感信息
    """
    sets = db.query('''
        SELECT SQL_CALC_FOUND_ROWS a.*, tp.name AS proj_name 
        FROM afflatus AS a
            INNER JOIN
            travel_proj as tp
                ON tp.id = a.proj_id
        ORDER BY id DESC 
        LIMIT $offset, $length;'''
        , vars = locals())
    totals = db.query('SELECT FOUND_ROWS() AS totals;')
    return storage(result = sets, totalrecords = totals[0].totals)
